#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sqlite3
import urllib
import time
import threading
import sys
import json

if sys.getdefaultencoding() != 'utf-8':
    reload(sys)
    sys.setdefaultencoding('utf-8')

class DbHelper:
    mDbPath=''

    '''
    CREATE TABLE 
    '''
    def __init__(self, dbpath):
        self.lock = threading.RLock() #锁  
        self.mDbPath = dbpath
        self.conn = sqlite3.connect(dbpath, check_same_thread=False)
        c = self.conn.cursor()
        
        c.execute('''CREATE TABLE if not exists "zoneCategorys" ( `id` INTEGER, `name` TEXT, `cover_img` TEXT, PRIMARY KEY(`id`) )''')
        c.execute('''CREATE TABLE if not exists "slugs" ( `id` INTEGER, `name` TEXT, `cover_img` TEXT, `type` INTEGER, PRIMARY KEY(`id`) )''')
        c.execute('''CREATE TABLE if not exists `video` (
	`id`	INTEGER,
	`cover_img`	TEXT,
	`is_collect`	INTEGER,
	`title`	TEXT,
	`play_num`	INTEGER,
	`time`	INTEGER,
	`create_at`	INTEGER,
	`url`	TEXT,
	`summary`	TEXT,
	`type1`	TEXT,
	`type2`	TEXT,
	PRIMARY KEY(`id`)
);''')

        #is_collect means download 
        print "Table created successfully"
        self.conn.commit()

    def insertAVideo(self, i, url, summary):
        ret = False
        c = self.conn.cursor()
        #http://www.a244b.cn/pic/oumei/1siwazujiao/b24.jpg
        cover_img = i['cover_img']
        typelist = cover_img[cover_img.find('pic/')+4:cover_img.rfind('/')].split('/')
        sql = '''insert into video values(?,?,?,?,?,?,?,?,?,?,?)'''
        if(len(typelist) == 2):
            para = (i['id'], i['cover_img'], 0, i['title'], i['play_num'], i['time'],i['create_at'], url, summary,typelist[0], typelist[1])
        else:
            para = (i['id'], i['cover_img'], 0, i['title'], i['play_num'], i['time'],i['create_at'], url, summary,'', '')
        try:
            c.execute(sql,para)
            self.conn.commit()
            ret = True
        except sqlite3.IntegrityError as e:
            print(e)
        return ret

    def setDownloadFinshed(self, id, url, sum):
        c = self.conn.cursor()
        sql = '''update video set is_collect=1,url=?,summary=? where id=?'''
        para = (url, sum, id)
        try:
            c.execute(sql,para)
        except sqlite3.IntegrityError as e:
            print(e)
        self.conn.commit()
        
    def insertslug(self, content):
        c = self.conn.cursor()
        j = json.loads(content)
        for i in j['data']:
            sql="insert into slugs values(%s, '%s', '%s', %s)" % (i['id'], i['name'], i['cover_img'], i['type'])
            try:
                c.execute(sql)
            except sqlite3.IntegrityError as e:
                print(e)
        self.conn.commit()


    def insertALine(self, id, name, cover_img):
        c = self.conn.cursor()
        sql="insert into zoneCategorys (id, name,cover_img) values('%s', '%s', '%s')" % (id, name, cover_img)
        try:
            c.execute(sql)
        except sqlite3.IntegrityError as e:
            print(e)

    def getEmails(self, count):
        emails = []
        c = self.conn.cursor()
        try:
            cursor = c.execute("select email from profile where id=-2 limit %d" %(count))
            for row in cursor:
                emails.append(row[0])
        except sqlite3.IntegrityError as e:
            print(e)
        except sqlite3.OperationalError as e:
            print e
        return emails

    def commit(self):
        self.conn.commit()

    def insertzoneCategorys(self, content):
        j = json.loads(content)
        for i in j['data']['data']:
            self.insertALine(i['id'],i['name'], i['cover_img'])
        print "commit"
        self.commit()
    
    def importintodb(self, filepath):
        file = open(filepath) 
        while 1:
            lines = file.readlines(1000000)
            if not lines:
                break
            print(len(lines))
            for line in lines:
                self.insertzoneCategorys(line)
        file.close()


    def getvideoids(self):
        emails = []
        c = self.conn.cursor()
        try:
            cursor = c.execute("select id from video where is_collect=0")
            for row in cursor:
                emails.append(row[0])
        except sqlite3.IntegrityError as e:
            print(e)
        except sqlite3.OperationalError as e:
            print e
        return emails

    def gettypes(self, count=-1):
        emails = []
        c = self.conn.cursor()
        try:
            cursor = c.execute("select id from slugs limit %d" %(count))
            for row in cursor:
                emails.append(row[0])
        except sqlite3.IntegrityError as e:
            print(e)
        except sqlite3.OperationalError as e:
            print e
        return emails

if __name__ == "__main__":
    Tstdb = DbHelper('qiezivideo.db')
    Tstdb.importintodb('tstdata.txt')
    emails=Tstdb.getEmails(10)
    print(emails)

    #Tstdb.insertALine(1, "testcontent")
    #Tstdb.refreshpassword()
    #Tstdb.importnewssid(sys.argv[1])

